﻿//============================
//数据库处理类
//============================
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace DBUtility
{
    public enum UpdateBehavior
    {
        Standard,	  //不继续，不回滚
        Continue,	  //Update command 将试着更新剩余的行
        Transactional //回滚（取消所用更新操作）
    }

    /// <summary>
    /// 数据访问辅助类，此类不允许继承和实例化
    /// </summary>
    public sealed class SqlHelper
    {
        #region 字段
        private static readonly string _connString = ConfigurationManager.ConnectionStrings["ConnPoolStr"].ConnectionString;
        #endregion

        #region 构造器
        private SqlHelper() { }
        #endregion

        #region 属性
        /// <summary>
        /// 使用了连接池的连接字符串
        /// </summary>
        public static string ConnPoolStr
        {
            get { return _connString; }
        }
        #endregion

        #region 共有方法
        /// <summary>
        /// 创建默认的连接,只是返回此连接实例，并没有打开连接，需要手动释放资源。
        /// </summary>
        /// <returns>SqlConnection的实例</returns>
        public static SqlConnection CreateConnection()
        {
            return CreateConnection(_connString);
        }

        /// <summary>
        /// 创建指定的连接,只是返回此连接实例，并没有打开连接，需要手动释放资源。
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>SqlConnection的实例</returns>
        public static SqlConnection CreateConnection(string connectionString)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            return connection;
        }

        #region ExecuteReader()
        /// <summary>
        /// 返回一个Reader，注意:在执行该命令时，如果关闭DataReader对象，则关联的Connection对象也将关闭。
        /// </summary>
        /// <param name="commandText">Sql语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string commandText)
        {
            return ExecuteReader(commandText, null, _connString);
        }

        /// <summary>
        /// 返回一个Reader，注意:在执行该命令时，如果关闭DataReader对象，则关联的Connection对象也将关闭。
        /// </summary>
        /// <param name="commandText">Sql语句</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string commandText, string connectionString)
        {
            return ExecuteReader(commandText, null, connectionString);
        }

        /// <summary>
        /// 返回一个Reader，注意:在执行该命令时，如果关闭DataReader对象，则关联的Connection对象也将关闭。
        /// </summary>
        /// <param name="commandText">Sql语句</param>
        /// <param name="cmdParms">参数数组</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string commandText, SqlParameter[] cmdParms)
        {
            return ExecuteReader(commandText, cmdParms, _connString);
        }

        /// <summary>
        /// 返回一个Reader，注意:在执行该命令时，如果关闭DataReader对象，则关联的Connection对象也将关闭。
        /// </summary>
        /// <param name="commandText">Sql语句</param>
        /// <param name="cmdParms">参数数组</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string commandText, SqlParameter[] cmdParms, string connectionString)
        {
            SqlConnection conn = OpenConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, conn, null, commandText, cmdParms);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return dr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }


        #endregion

        #region ExecuteNonQuery()
        /// <summary>
        /// 对连接执行T-SQL语句，并返回受影响的行数。
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(commandText, null, _connString);
        }

        /// <summary>
        /// 对连接执行T-SQL语句，并返回受影响的行数。
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(string commandText, string connectionString)
        {
            return ExecuteNonQuery(commandText, null, connectionString);
        }

        /// <summary>
        /// 对连接执行T-SQL语句，并返回受影响的行数。
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="trans">事务</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(string commandText, SqlTransaction trans)
        {
            return ExecuteNonQuery(commandText, null, trans);
        }

        /// <summary>
        /// 对连接执行T-SQL语句，并返回受影响的行数。
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(string commandText, SqlParameter[] cmdParms)
        {
            return ExecuteNonQuery(commandText, cmdParms, _connString);
        }

        /// <summary>
        /// 对连接执行T-SQL语句，并返回受影响的行数。
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="isBig">如果为true，则设置超时时间为120，flase为系统默认</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(string commandText, bool isBig)
        {
            SqlCommand cmd = new SqlCommand();

            if (isBig) cmd.CommandTimeout = 120;

            using (SqlConnection conn = OpenConnection(_connString))
            {
                PrepareCommand(cmd, conn, null, commandText, null);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 对连接执行T-SQL语句，并返回受影响的行数。
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(string commandText, SqlParameter[] cmdParms, string connectionString)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = OpenConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, commandText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Dispose();
                conn.Close();
                return val;
            }
        }

        /// <summary>
        /// 对连接执行T-SQL语句，并返回受影响的行数。
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="cmdParms">参数</param>		
        /// <param name="trans">事务,SqlConnection需要有用户自己定义，这里就通过事务的属性得到连接</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(string commandText, SqlParameter[] cmdParms, SqlTransaction trans)
        {
            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, trans.Connection, trans, commandText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
        #endregion

        #region ExecuteScalar()
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(string commandText)
        {
            return ExecuteScalar(commandText, null, _connString);
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(string commandText, string connectionString)
        {
            return ExecuteScalar(commandText, null, connectionString);
        }

        /// <summary>
        ///  执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(string commandText, SqlParameter[] cmdParms)
        {
            return ExecuteScalar(commandText, cmdParms, _connString);
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <param name="connectionString">查询字符串</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(string commandText, SqlParameter[] cmdParms, string connectionString)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = OpenConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, commandText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                cmd.Dispose();
                conn.Close();
                return val;
            }
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// 2012-12-4 jerzy ADD
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <param name="_tran">事务实例</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(string commandText, SqlTransaction _tran)
        {
            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, _tran.Connection, _tran, commandText, null);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            cmd.Dispose();
            return val;
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// 2012-12-4 jerzy ADD
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <param name="_tran">事务实例</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(string commandText, SqlParameter[] cmdParms, SqlTransaction _tran)
        {
            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, _tran.Connection, _tran, commandText, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            cmd.Dispose();
            return val;
        }

        #endregion

        #region FillDataSet()
        /// <summary>
        /// 在DataSet指定范围中添加或刷新行以匹配使用DataSet和DataTable名称中的数据源中的行。
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <param name="ds">被引用的DataSet</param>
        /// <param name="tableName">dataset中的表名</param>
        /// <param name="isCover">是否覆盖dataset中现存的同名表</param>
        public static void FillDataset(string commandText, DataSet ds, string tableName, bool isCover)
        {
            FillDataset(commandText, ds, tableName, null, _connString, isCover);
        }

        /// <summary>
        /// 在DataSet指定范围中添加或刷新行以匹配使用DataSet和DataTable名称中的数据源中的行。
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <param name="ds">被引用的DataSet</param>
        /// <param name="tableName">dataset中的表名</param>
        /// <param name="cmdParms"></param>
        /// <param name="isCover">是否覆盖已经存在的名称相同的</param>
        public static void FillDataset(string commandText, DataSet ds, string tableName, SqlParameter[] cmdParms, bool isCover)
        {
            FillDataset(commandText, ds, tableName, cmdParms, _connString, isCover);
        }


        /// <summary>
        /// 在DataSet指定范围中添加或刷新行以匹配使用DataSet和DataTable名称中的数据源中的行。
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <param name="ds">被引用的DataSet</param>
        /// <param name="tableName">dataset中的表名</param>
        /// <param name="connectionString">指定的连接字符串</param>
        /// <param name="isCover">是否覆盖已经存在的名称相同的Table</param>
        public static void FillDataset(string commandText, DataSet ds, string tableName, string connectionString, bool isCover)
        {
            FillDataset(commandText, ds, tableName, null, connectionString, isCover);
        }

        /// <summary>
        /// 在DataSet指定范围中添加或刷新行以匹配使用DataSet和DataTable名称中的数据源中的行。
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <param name="ds">被引用的DataSet</param>
        /// <param name="tableName">dataset中的表名</param>
        /// <param name="connectionString">指定的连接字符串</param>
        /// <param name="isCover">是否覆盖已经存在的名称相同的Table</param>
        public static void FillDataset(string commandText, DataSet ds, string tableName, SqlParameter[] cmdParms, string connectionString, bool isCover)
        {
            if (isCover)
                if (ds.Tables[tableName] != null)
                    ds.Tables.Remove(tableName);
            DoLoadDataSet(commandText, ds, tableName, cmdParms, null, connectionString);
        }
        #endregion

        #region ExecuteDataSet()
        /// <summary>
        /// 执行T-SQL语句，并返回包含执行结果的DataSet
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataset(string commandText)
        {
            return ExecuteDataset(commandText, null, _connString);
        }

        /// <summary>
        ///  执行T-SQL语句，并返回包含执行结果的DataSet
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataset(string commandText, string connectionString)
        {
            return ExecuteDataset(commandText, null, connectionString);
        }

        /// <summary>
        /// 执行T-SQL语句，并返回包含执行结果的DataSet
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataset(string commandText, SqlParameter[] cmdParms)
        {
            return ExecuteDataset(commandText, cmdParms, _connString);
        }
        /// <summary>
        /// This method is used to attach array of SqlParameters to a SqlCommand.
        /// 
        /// This method will assign a value of DbNull to any parameter with a direction of
        /// InputOutput and a value of null.  
        /// 
        /// This behavior will prevent default values from being used, but
        /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
        /// where the user provided no input value.
        /// </summary>
        /// <param name="command">The command to which the parameters will be added</param>
        /// <param name="commandParameters">An array of SqlParameters to be added to command</param>
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }

        /// <summary>
        /// This method assigns dataRow column values to an array of SqlParameters
        /// </summary>
        /// <param name="commandParameters">Array of SqlParameters to be assigned values</param>
        /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
        private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
        {
            if ((commandParameters == null) || (dataRow == null))
            {
                // Do nothing if we get no data
                return;
            }

            int i = 0;
            // Set the parameters values
            foreach (SqlParameter commandParameter in commandParameters)
            {
                // Check the parameter name
                if (commandParameter.ParameterName == null ||
                    commandParameter.ParameterName.Length <= 1)
                    throw new Exception(
                        string.Format(
                            "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
                            i, commandParameter.ParameterName));
                if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
                    commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
                i++;
            }
        }

        /// <summary>
        /// This method assigns an array of values to an array of SqlParameters
        /// </summary>
        /// <param name="commandParameters">Array of SqlParameters to be assigned values</param>
        /// <param name="parameterValues">Array of objects holding the values to be assigned</param>
        private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null))
            {
                // Do nothing if we get no data
                return;
            }

            // We must have the same number of values as we pave parameters to put them in
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("Parameter count does not match Parameter Value count.");
            }

            // Iterate through the SqlParameters, assigning the values from the corresponding position in the 
            // value array
            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {
                // If the current array value derives from IDbDataParameter, then assign its Value property
                if (parameterValues[i] is IDbDataParameter)
                {
                    IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
                    if (paramInstance.Value == null)
                    {
                        commandParameters[i].Value = DBNull.Value;
                    }
                    else
                    {
                        commandParameters[i].Value = paramInstance.Value;
                    }
                }
                else if (parameterValues[i] == null)
                {
                    commandParameters[i].Value = DBNull.Value;
                }
                else
                {
                    commandParameters[i].Value = parameterValues[i];
                }
            }
        }
        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
        /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// e.g.:  
        ///  DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
        /// <returns>A dataset containing the resultset generated by the command</returns>
        public static DataSet ExecuteDataset(string spName, params object[] parameterValues)
        {
            if (_connString == null || _connString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // If we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(_connString, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes an array of SqlParameters
                return ExecuteDataset(_connString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteDataset(_connString, CommandType.StoredProcedure, spName);
            }
        }
        /// <summary>
        /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <returns>A dataset containing the resultset generated by the command</returns>
        public static DataSet ExecuteDataset(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (_connString == null || _connString.Length == 0) throw new ArgumentNullException("connectionString");

            // Create & open a SqlConnection, and dispose of it after we are done
            using (SqlConnection connection = new SqlConnection(_connString))
            {
                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                return ExecuteDataset(connection, commandType, commandText, commandParameters);
            }
        }
        /// <summary>
        /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <returns>A dataset containing the resultset generated by the command</returns>
        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // Create the DataAdapter & DataSet
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();

                // Fill the DataSet using default values for DataTable names, etc
                da.Fill(ds);

                // Detach the SqlParameters from the command object, so they can be used again
                cmd.Parameters.Clear();

                if (mustCloseConnection)
                    connection.Close();

                // Return the dataset
                return ds;
            }
        }

        /// <summary>
        /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
        /// to the provided command
        /// </summary>
        /// <param name="command">The SqlCommand to be prepared</param>
        /// <param name="connection">A valid SqlConnection, on which to execute this command</param>
        /// <param name="transaction">A valid SqlTransaction, or 'null'</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">An array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
        /// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            // If the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }

            // Associate the connection with the command
            command.Connection = connection;

            // Set the command text (stored procedure name or SQL statement)
            command.CommandText = commandText;

            // If we were provided a transaction, assign it
            if (transaction != null)
            {
                if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }

            // Set the command type
            command.CommandType = commandType;

            // Attach the command parameters if they are provided
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }
        /// <summary>
        /// 执行T-SQL语句，并返回包含执行结果的DataSet
        /// </summary>
        /// <param name="commandText">T-SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataset(string commandText, SqlParameter[] cmdParms, string connectionString)
        {
            DataSet ds = new DataSet();
            DoLoadDataSet(commandText, ds, "table", cmdParms, null, connectionString);
            return ds;
        }
        #endregion

        #region UpdateDataSet()
        /// <summary>
        /// 为具有指定名称的Table的DataSet中每个已插入、已更新或一删除的行调用相应的Insert、Update、或Delete语句。
        /// </summary>
        /// <param name="selectCommand">T-SQL语句</param>
        /// <param name="ds">DataSet</param>
        /// <param name="tableName">TableName</param>
        /// <returns>影响的行数</returns>
        public static int UpdateDataSet(string selectCommand, DataSet ds, string tableName)
        {
            //UpdateDataSet(selectCommand, ds, tableName, UpdateBehavior.Standard);

            //************************
            //注：在接口不变的情况下，暂时先用下边的方法，不支持事务处理。
            using (SqlConnection conn = OpenConnection(_connString))
            {
                SqlDataAdapter da = new SqlDataAdapter(selectCommand, conn);
                SqlCommandBuilder cb = new SqlCommandBuilder(da);
                return da.Update(ds, tableName);
            }
            //************************
        }

        /// <summary>
        /// 为具有指定名称的Table的DataSet中每个已插入、已更新或一删除的行调用相应的Insert、Update、或Delete语句。
        /// </summary>
        /// <param name="selectCommand">T-SQL语句</param>
        /// <param name="ds">DataSet</param>
        /// <param name="tableName">TableName</param>
        /// <param name="updateBehavior">UpdateBehavior枚举值</param>
        //		public static void UpdateDataSet(string selectCommand, DataSet ds, string tableName, UpdateBehavior updateBehavior )
        //		{
        //			SqlDataAdapter da = new SqlDataAdapter(selectCommand, new SqlConnection(_connString));
        //			SqlCommandBuilder cb = new SqlCommandBuilder(da);
        //			UpdateDataSet(ds, tableName, cb, updateBehavior, _connString);			
        //		}
        #endregion

        #region ExecuteQueryBatch()
        /// <summary>
        /// 批量执行SQL语句,包括INSERT,UPDATE,DELETE
        /// </summary>
        /// <param name="sqlArray">批量执行的SQL字符串数组</param>
        /// <returns></returns>
        public static int ExecuteQueryBatch(String[] sqlArray)
        {
            string msg;
            return ExecuteQueryBatch(sqlArray, out msg);
        }
        public static int ExecuteQueryBatch(String[] sqlArray, out string ErrorMsg)
        {
            ErrorMsg = "";
            int iReturn = 0;
            using (SqlConnection conn = OpenConnection(_connString))
            {
                SqlTransaction tran = conn.BeginTransaction();//启用事务
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.Transaction = tran;
                try
                {
                    for (int i = 0; i < sqlArray.Length; i++)
                    {
                        cmd.CommandText = sqlArray[i];
                        iReturn += cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                    return iReturn;
                }
                catch (Exception ex)
                {
                    ErrorMsg = ex.ToString();
                    tran.Rollback();
                    return -1;
                }
            }
        }
        #endregion

        #region ExecuteInsertDataTable（）
        /// <summary>
        /// 直接用DataTabel数据类型插入数据库中指定的表中
        /// </summary>
        /// <param name="tb">DataTable数据</param>
        /// <param name="tabName">需要插入的表</param>
        /// <returns></returns>
        public static bool ExecuteInsertDataTable(DataTable tb, string tabName)
        {
            bool res = true;
            try
            {
                SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(_connString, SqlBulkCopyOptions.UseInternalTransaction);
                sqlbulkcopy.DestinationTableName = tabName;//数据库中表名
                sqlbulkcopy.WriteToServer(tb);
            }
            catch (Exception ex)
            {
                res = false;
            }
            return res;
        }
        #endregion

        /// <summary>
        /// 获取分页的的记录
        /// jerzyjiang 2013-11-11
        /// </summary>
        /// <param name="_tableOrViewName">表或视图或查询语句</param>
        /// <param name="_isSelectSQL">_tableOrViewName是否带select的查询语句：1-是 0-不是</param>
        /// <param name="_isReturnRecord">是否返回记录：1-是 0-不是</param>
        /// <param name="_whereStr">where后面的查询条件（不需要对单引号进行预处理）</param>
        /// <param name="_orderStr">order by 后面用于排序的子句</param>
        /// <param name="_filedStr">要返回的字段名，全部用“*”</param>
        /// <param name="_pageIndex">要返回的页数，从1开始</param>
        /// <param name="_pageSize">每页记录大小</param>
        /// <param name="_total">返回本次查询能够查到的所有记录数目</param>
        /// <param name="_recordCount">本次查到返回的记录数目</param>
        /// <param name="_pageCount">返回本次查询能够返回的页数 </param>
        /// <param name="_message">返回执行结果信息</param>
        /// <returns></returns>
        public static DataTable GetPageData(string _tableOrViewName, int _isSelectSQL, int _isReturnRecord, string _whereStr, string _orderStr, string _filedStr, int _pageIndex, int _pageSize, ref int _total, ref int _recordCount, ref int _pageCount, ref string _message)
        {
            Exception newException = null;
            DataTable dt = null;
            SqlConnection conn = SqlHelper.CreateConnection();
            conn.Open();
            try
            {
                SqlCommand cmd = new SqlCommand("SP_GetPageData", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@TVName", SqlDbType.NVarChar, 1000);
                cmd.Parameters["@TVName"].Value = _tableOrViewName;

                cmd.Parameters.Add("@IsSelectSQL", SqlDbType.Bit);
                cmd.Parameters["@IsSelectSQL"].Value = _isSelectSQL;

                cmd.Parameters.Add("@IsReturnRecord", SqlDbType.Bit);
                cmd.Parameters["@IsReturnRecord"].Value = _isReturnRecord;

                cmd.Parameters.Add("@PageSize", SqlDbType.Int);
                cmd.Parameters["@PageSize"].Value = _pageSize;

                cmd.Parameters.Add("@PageIndex", SqlDbType.Int);
                cmd.Parameters["@PageIndex"].Value = _pageIndex;

                //cmd.Parameters.Add("@WhereStr", SqlDbType.NVarChar, 1000);
                cmd.Parameters.Add("@WhereStr", SqlDbType.VarChar);//jintangliang 2014-9-18 Varchar(Max)
                cmd.Parameters["@WhereStr"].Value = _whereStr;//.Replace("'","''"); //转换单引号

                cmd.Parameters.Add("@OrderStr", SqlDbType.NVarChar, 400);
                cmd.Parameters["@OrderStr"].Value = _orderStr;

                cmd.Parameters.Add("@FieldStr", SqlDbType.NVarChar, 1000);
                cmd.Parameters["@FieldStr"].Value = string.IsNullOrEmpty(_filedStr) ? "*" : _filedStr;

                cmd.Parameters.Add("@AllRecordCount", SqlDbType.Int);
                cmd.Parameters["@AllRecordCount"].Value = 0;
                cmd.Parameters["@AllRecordCount"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add("@RecordCount", SqlDbType.Int);
                cmd.Parameters["@RecordCount"].Value = 0;
                cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add("@PageCount", SqlDbType.Int);
                cmd.Parameters["@PageCount"].Value = 0;
                cmd.Parameters["@PageCount"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add("@RMessage", SqlDbType.NVarChar, 4000);
                cmd.Parameters["@RMessage"].Value = "";
                cmd.Parameters["@RMessage"].Direction = ParameterDirection.Output;

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    if (ds.Tables.Count > 0)
                        dt = ds.Tables[0];
                }

                _total = Convert.ToInt32(cmd.Parameters["@AllRecordCount"].Value);
                _recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                _pageCount = Convert.ToInt32(cmd.Parameters["@PageCount"].Value);
                _message = Convert.ToString(cmd.Parameters["@RMessage"].Value);

                cmd.Parameters.Clear();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                newException = ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            if (newException != null) throw newException;

            return dt;
        }
        #endregion

        #region 私有方法
        /// <summary>
        /// 打开指定的连接
        /// </summary>
        /// <param name="connectionString">指定的连接字符串</param>
        /// <returns>已经打开的连接</returns>
        private static SqlConnection OpenConnection(string connectionString)
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(connectionString);
                connection.Open();
            }
            catch
            {
                if (connection != null)
                    connection.Close();
                throw;
            }
            return connection;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn,
            SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandTimeout = 180;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = CommandType.Text;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        /// <summary>
        /// 在DataSet指定范围中添加或刷新行以匹配使用DataSet和DataTable名称中的数据源中的行。
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="ds"></param>
        /// <param name="tableName"></param>
        /// <param name="cmdParms"></param>
        /// <param name="trans"></param>
        /// <param name="connectionString"></param>
        private static void DoLoadDataSet(string commandText, DataSet ds, string tableName,
            SqlParameter[] cmdParms, SqlTransaction trans, string connectionString)
        {
            using (SqlConnection conn = OpenConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandTimeout = 180;
                PrepareCommand(cmd, conn, trans, commandText, cmdParms);
                using (SqlDataAdapter ada = GetDataAdapter(UpdateBehavior.Standard, conn))
                {
                    ada.SelectCommand = cmd;
                    ada.Fill(ds, tableName);
                }
                cmd.Parameters.Clear();
                cmd.Dispose();
                conn.Close();
            }
        }

        private static SqlTransaction BeginTransaction(SqlConnection connection)
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                SqlTransaction tran = connection.BeginTransaction();
                return tran;
            }
            catch { throw; }
        }

        private static SqlDataAdapter GetDataAdapter(UpdateBehavior updateBehavior, SqlConnection connection)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(String.Empty, connection);

            if (updateBehavior == UpdateBehavior.Continue)
            {
                adapter.RowUpdated += new SqlRowUpdatedEventHandler(OnSqlRowUpdated);
            }
            return adapter;
        }

        private static int UpdateDataSet(DataSet ds, string tableName,
            SqlCommandBuilder cb, UpdateBehavior updateBehavior, string connectionString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                if (updateBehavior == UpdateBehavior.Transactional)
                {
                    SqlTransaction trans = BeginTransaction(conn);
                    try
                    {
                        int rowsAffected = UpdateDataSet(ds, tableName, cb, trans);
                        trans.Commit();
                        return rowsAffected;
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
                else
                {
                    return DoUpdateDataSet(updateBehavior, conn, ds, tableName, cb);
                }
            }
        }

        private static int UpdateDataSet(DataSet ds, string tableName,
            SqlCommandBuilder cb, SqlTransaction trans)
        {
            cb.GetInsertCommand().Transaction = trans;
            cb.GetUpdateCommand().Transaction = trans;
            cb.GetDeleteCommand().Transaction = trans;

            return DoUpdateDataSet(UpdateBehavior.Transactional, trans.Connection, ds, tableName, cb);
        }

        private static int DoUpdateDataSet(UpdateBehavior behavior, SqlConnection connection,
            DataSet ds, string tableName, SqlCommandBuilder cb)
        {
            using (SqlDataAdapter adapter = GetDataAdapter(behavior, connection))
            {
                adapter.InsertCommand = cb.GetInsertCommand();
                adapter.UpdateCommand = cb.GetUpdateCommand();
                adapter.DeleteCommand = cb.GetDeleteCommand();

                try
                {
                    return adapter.Update(ds.Tables[tableName]);
                }
                catch
                {
                    throw;
                }
            }
        }

        private static void OnSqlRowUpdated(object sender, SqlRowUpdatedEventArgs rowThatCouldNotBeWritten)
        {
            if (rowThatCouldNotBeWritten.RecordsAffected == 0)
            {
                if (rowThatCouldNotBeWritten.Errors != null)
                {
                    rowThatCouldNotBeWritten.Row.RowError = "更新行失败！";
                    rowThatCouldNotBeWritten.Status = UpdateStatus.SkipCurrentRow;
                }
            }
        }
        #endregion
    }

    /// <summary>
    /// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
    /// ability to discover parameters for stored procedures at run-time.
    /// </summary>
    public sealed class SqlHelperParameterCache
    {
        #region private methods, variables, and constructors

        //Since this class provides only static methods, make the default constructor private to prevent 
        //instances from being created with "new SqlHelperParameterCache()"
        private SqlHelperParameterCache() { }

        private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// Resolve at run time the appropriate set of SqlParameters for a stored procedure
        /// </summary>
        /// <param name="connection">A valid SqlConnection object</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="includeReturnValueParameter">Whether or not to include their return value parameter</param>
        /// <returns>The parameter array discovered.</returns>
        private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            SqlCommand cmd = new SqlCommand(spName, connection);
            cmd.CommandType = CommandType.StoredProcedure;

            connection.Open();
            SqlCommandBuilder.DeriveParameters(cmd);
            connection.Close();

            if (!includeReturnValueParameter)
            {
                cmd.Parameters.RemoveAt(0);
            }

            SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

            cmd.Parameters.CopyTo(discoveredParameters, 0);

            // Init the parameters with a DBNull value
            foreach (SqlParameter discoveredParameter in discoveredParameters)
            {
                discoveredParameter.Value = DBNull.Value;
            }
            return discoveredParameters;
        }

        /// <summary>
        /// Deep copy of cached SqlParameter array
        /// </summary>
        /// <param name="originalParameters"></param>
        /// <returns></returns>
        private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
        {
            SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
            }

            return clonedParameters;
        }

        #endregion private methods, variables, and constructors

        #region caching functions

        /// <summary>
        /// Add parameter array to the cache
        /// </summary>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">An array of SqlParamters to be cached</param>
        public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            string hashKey = connectionString + ":" + commandText;

            paramCache[hashKey] = commandParameters;
        }

        /// <summary>
        /// Retrieve a parameter array from the cache
        /// </summary>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <returns>An array of SqlParamters</returns>
        public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            string hashKey = connectionString + ":" + commandText;

            SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
            if (cachedParameters == null)
            {
                return null;
            }
            else
            {
                return CloneParameters(cachedParameters);
            }
        }

        #endregion caching functions

        #region Parameter Discovery Functions

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure
        /// </summary>
        /// <remarks>
        /// This method will query the database for this information, and then store it in a cache for future requests.
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <returns>An array of SqlParameters</returns>
        public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
        {
            return GetSpParameterSet(connectionString, spName, false);
        }

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure
        /// </summary>
        /// <remarks>
        /// This method will query the database for this information, and then store it in a cache for future requests.
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
        /// <returns>An array of SqlParameters</returns>
        public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
            }
        }

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure
        /// </summary>
        /// <remarks>
        /// This method will query the database for this information, and then store it in a cache for future requests.
        /// </remarks>
        /// <param name="connection">A valid SqlConnection object</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <returns>An array of SqlParameters</returns>
        internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
        {
            return GetSpParameterSet(connection, spName, false);
        }

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure
        /// </summary>
        /// <remarks>
        /// This method will query the database for this information, and then store it in a cache for future requests.
        /// </remarks>
        /// <param name="connection">A valid SqlConnection object</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
        /// <returns>An array of SqlParameters</returns>
        internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone())
            {
                return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
            }
        }

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure
        /// </summary>
        /// <param name="connection">A valid SqlConnection object</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
        /// <returns>An array of SqlParameters</returns>
        private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");

            SqlParameter[] cachedParameters;

            cachedParameters = paramCache[hashKey] as SqlParameter[];
            if (cachedParameters == null)
            {
                SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
                paramCache[hashKey] = spParameters;
                cachedParameters = spParameters;
            }

            return CloneParameters(cachedParameters);
        }

        #endregion Parameter Discovery Functions
    }
}